create table WHS_OTHER_TASK
(
    UUID VARCHAR2(32) not null
        constraint WHS_OTHER_TASK_PK
            primary key,
    SPMS_ID VARCHAR2(32),
    MOVEMENT_TYPE VARCHAR2(32),
    WERKS VARCHAR2(6),
    WHS_LOCATION_CODE VARCHAR2(6),
    SAP_VOUCHER_NUMBER VARCHAR2(32),
    SAP_VOUCHER_YEAR VARCHAR2(8),
    DOCUMENT_DATE VARCHAR2(10),
    POSTING_DATE VARCHAR2(10),
    CREATE_USERID VARCHAR2(20),
    SAP_PO VARCHAR2(32),
    SAP_ORDER_TYPE VARCHAR2(32),
    PURCHASE_GROUP VARCHAR2(6),
    PURCHASE_GROUP_NAME VARCHAR2(64),
    RELATED_VOUCHER_NUMBER VARCHAR2(32),
    RELATED_VOUCHER_YEAR VARCHAR2(8),
    RELATED_DOCUMENT_DATE VARCHAR2(10),
    RELATED_POSTING_DATE VARCHAR2(10),
    COST_CENTER VARCHAR2(16),
    PACKAGE_LIST VARCHAR2(16),
    GOODS_TYPE VARCHAR2(30),
    TASK_STATUS VARCHAR2(4),
    CREATE_DATE VARCHAR2(10),
    CREATE_TIME DATE default TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),
    PACKAGE_NOTE VARCHAR2(50),
    DELIVERY_NOTE_SRM VARCHAR2(35),
    RELATED_TASK_ID VARCHAR2(64),
    UPDATE_TIME DATE,
    REMARKS VARCHAR2(255),
    DELIVERY_NOTE_SAP VARCHAR2(32),
    SAP_MOVEMENT_TYPE VARCHAR2(16),
    WERKS_MOVE_OUT VARCHAR2(6),
    WERKS_MOVE_IN VARCHAR2(6),
    WHS_LOCATION_CODE_MOVE_OUT VARCHAR2(6),
    WHS_LOCATION_CODE_MOVE_IN VARCHAR2(6),
    MOVE_OUT_REMARK VARCHAR2(255),
    MOVE_IN_REMARK VARCHAR2(255)
)
/

comment on table WHS_OTHER_TASK is '异常出入库任务表'
/

comment on column WHS_OTHER_TASK.UUID is '异常出入库任务表主键UUID'
/

comment on column WHS_OTHER_TASK.SPMS_ID is '异常出入库SPMS单号'
/

comment on column WHS_OTHER_TASK.MOVEMENT_TYPE is '移动类型 卸货入库-311 赠品入库-511(对冲512) 串货调整-309(对冲310）报废-551(冲销552)
样品领用-Z09(冲销Z10) 仓库工具领用-Z21(冲销Z22) 其他公司领用-Z23(冲销Z24) 退货出库-161(冲销162) 委外加工发货出库-541(冲销542)'
/

comment on column WHS_OTHER_TASK.WERKS is '工厂代码'
/

comment on column WHS_OTHER_TASK.WHS_LOCATION_CODE is '仓库代码'
/

comment on column WHS_OTHER_TASK.SAP_VOUCHER_NUMBER is 'SAP凭证号'
/

comment on column WHS_OTHER_TASK.SAP_VOUCHER_YEAR is 'SAP凭证年份'
/

comment on column WHS_OTHER_TASK.DOCUMENT_DATE is 'SAP凭证日期'
/

comment on column WHS_OTHER_TASK.POSTING_DATE is '过帐日期'
/

comment on column WHS_OTHER_TASK.CREATE_USERID is '操作员ID'
/

comment on column WHS_OTHER_TASK.SAP_PO is 'SAP采购订单号'
/

comment on column WHS_OTHER_TASK.SAP_ORDER_TYPE is 'SAP采购订单类型'
/

comment on column WHS_OTHER_TASK.PURCHASE_GROUP is '采购组'
/

comment on column WHS_OTHER_TASK.PURCHASE_GROUP_NAME is '采购组名称'
/

comment on column WHS_OTHER_TASK.RELATED_VOUCHER_NUMBER is '关联凭证号'
/

comment on column WHS_OTHER_TASK.RELATED_VOUCHER_YEAR is '关联凭证年度'
/

comment on column WHS_OTHER_TASK.RELATED_DOCUMENT_DATE is '关联凭证日期'
/

comment on column WHS_OTHER_TASK.RELATED_POSTING_DATE is '关联过帐日期'
/

comment on column WHS_OTHER_TASK.COST_CENTER is '成本中心'
/

comment on column WHS_OTHER_TASK.PACKAGE_LIST is '装箱单号'
/

comment on column WHS_OTHER_TASK.GOODS_TYPE is '货物类型 X-备件 M-贸易'
/

comment on column WHS_OTHER_TASK.TASK_STATUS is '单据状态 0-作废 1-草稿 2-操作中 3-待提交SAP 4-提交SAP成功 5-提交SAP失败 6-关闭'
/

comment on column WHS_OTHER_TASK.CREATE_DATE is '创建日期'
/

comment on column WHS_OTHER_TASK.CREATE_TIME is '创建时间，系统产生'
/

comment on column WHS_OTHER_TASK.PACKAGE_NOTE is '包装单号'
/

comment on column WHS_OTHER_TASK.DELIVERY_NOTE_SRM is 'SAP交货单号'
/

comment on column WHS_OTHER_TASK.RELATED_TASK_ID is '关联单据号码'
/

comment on column WHS_OTHER_TASK.UPDATE_TIME is '列新时间'
/

comment on column WHS_OTHER_TASK.REMARKS is '备注'
/

comment on column WHS_OTHER_TASK.DELIVERY_NOTE_SAP is 'SAP 内向交货单号'
/

comment on column WHS_OTHER_TASK.SAP_MOVEMENT_TYPE is 'SAP移动类型'
/

comment on column WHS_OTHER_TASK.WERKS_MOVE_OUT is '移出工厂代码'
/

comment on column WHS_OTHER_TASK.WERKS_MOVE_IN is '移入工厂代码'
/

comment on column WHS_OTHER_TASK.WHS_LOCATION_CODE_MOVE_OUT is '移出仓库代码'
/

comment on column WHS_OTHER_TASK.WHS_LOCATION_CODE_MOVE_IN is '移入仓库代码'
/

comment on column WHS_OTHER_TASK.MOVE_OUT_REMARK is '移出备注'
/

comment on column WHS_OTHER_TASK.MOVE_IN_REMARK is '移入备注'
/

create or replace trigger TR_WHS_OTHER_TASK_BEF
    before insert
    on WHS_OTHER_TASK
    for each row
DECLARE
    V_NUM1 NUMBER := 0;
BEGIN
    SELECT NVL(TO_NUMBER(SUBSTR(MAX(WO.SPMS_ID), -3, 3)), 0) INTO V_NUM1 FROM WHS_OTHER_TASK WO
    WHERE WO.CREATE_DATE = :NEW.CREATE_DATE
      AND WO.WERKS = :NEW.WERKS
      AND WO.WHS_LOCATION_CODE = :NEW.WHS_LOCATION_CODE;
    :NEW.SPMS_ID := :NEW.WERKS||'-'||:NEW.WHS_LOCATION_CODE||'-'||REPLACE(:NEW.CREATE_DATE, '-')||'-'|| SUBSTR(1001 + V_NUM1, 2, 4);
END;
/





INSERT INTO SPMS.ORDER_KEY_VALUE_CONFIG (UUID, FIELD_KEY, FIELD_NAME, KEY_CODE, KEY_CODE_DESC, KEY_CODE_EN_DESC, ENABLED, SORT_NO, REMARK) VALUES ('b265134fbf06453daa84cf969679c594', 'whsOtherStatus', '其它出入库状态', '0', '作废', 'Withdraw', '1', 1000, '4');
INSERT INTO SPMS.ORDER_KEY_VALUE_CONFIG (UUID, FIELD_KEY, FIELD_NAME, KEY_CODE, KEY_CODE_DESC, KEY_CODE_EN_DESC, ENABLED, SORT_NO, REMARK) VALUES ('53e7b662ff02473b8fb9c611c5c68f46', 'whsOtherStatus', '其它出入库状态', '1', '草稿', 'Draft', '1', 1000, '2');
INSERT INTO SPMS.ORDER_KEY_VALUE_CONFIG (UUID, FIELD_KEY, FIELD_NAME, KEY_CODE, KEY_CODE_DESC, KEY_CODE_EN_DESC, ENABLED, SORT_NO, REMARK) VALUES ('e0e154db0ca44f87946f1c95f4a5d52c', 'whsOtherStatus', '其它出入库状态', '2', '操作中', 'In Porcess', '1', 1000, '3');
INSERT INTO SPMS.ORDER_KEY_VALUE_CONFIG (UUID, FIELD_KEY, FIELD_NAME, KEY_CODE, KEY_CODE_DESC, KEY_CODE_EN_DESC, ENABLED, SORT_NO, REMARK) VALUES ('d1b10c79544746e4887b0ed9f5709c9e', 'whsOtherStatus', '其它出入库状态', '3', '待提交SAP', 'Pending SAP', '1', 1000, '5');
INSERT INTO SPMS.ORDER_KEY_VALUE_CONFIG (UUID, FIELD_KEY, FIELD_NAME, KEY_CODE, KEY_CODE_DESC, KEY_CODE_EN_DESC, ENABLED, SORT_NO, REMARK) VALUES ('99e6cb9cddb449a3b2a357ee7909ed5a', 'whsOtherStatus', '其它出入库状态', '4', '提交SAP成功', 'Submit SAP successed', '1', 1000, '6');
INSERT INTO SPMS.ORDER_KEY_VALUE_CONFIG (UUID, FIELD_KEY, FIELD_NAME, KEY_CODE, KEY_CODE_DESC, KEY_CODE_EN_DESC, ENABLED, SORT_NO, REMARK) VALUES ('d7541cab9f9647b0b9cbd282ecfd70ca', 'whsOtherStatus', '其它出入库状态', '5', '提交SAP失败', 'Submit SAP Failed', '1', 1000, '7');
INSERT INTO SPMS.ORDER_KEY_VALUE_CONFIG (UUID, FIELD_KEY, FIELD_NAME, KEY_CODE, KEY_CODE_DESC, KEY_CODE_EN_DESC, ENABLED, SORT_NO, REMARK) VALUES ('452887046e944e318889da5e20736d6e', 'whsOtherStatus', '其它出入库状态', '6', '关闭', 'Closed', '1', 1000, '7');
